<?php
defined('IN_YZMPHP') or exit('Access Denied');
class hr_xlswriter{
	public $excel;
	public $my_format=[];
	public $number_keys=[];
	public $center_keys=[];
	public $filename;
	public $keys;
	public $column = 1;
	public $page = 0;
	
	//初始化，读取配置
    public function __construct($type,$model='',$sheet_name='Sheet1'){
        $config = ['path' => $this->get_tmp_dir() . '/'];
		$excel = new \Vtiful\Kernel\Excel($config);
		//获取文件名
		$this->filename = $this->get_excel_filename($type);
		//自由内存模式，可能占内存过高
		if(!$model){
			$this->excel  = $excel->fileName($this->filename.'.xlsx', $sheet_name);
		}else{
			//固定内存模式 WPS 需要关闭 zip64
			$this->excel  = $excel->constMemory($this->filename.'.xlsx',$sheet_name,false);
		}
		//读锁
		if(getcache('LOCK::download_lock')){
			exit('有导出任务在执行，请稍后执行！');
		}
		//懒人锁
		//setcache('LOCK::download_lock',1,200);
		//自定义单元格格式初始化
		$this->format();
		//标题数据
		$this->insert_header($type);
    }
	
	//分页模式
	public function page(){
		$this->page = 2;
	}
	
	public function format(){
		$handle = $this->excel->getHandle();
        $format1    = new \Vtiful\Kernel\Format($handle);
        $format2    = new \Vtiful\Kernel\Format($handle);
		$format3    = new \Vtiful\Kernel\Format($handle);
		
        //标题格式
        $this->my_format['title_style'] = $format1->fontSize(10)//字体
            ->bold()//加粗
			->border(\Vtiful\Kernel\Format::BORDER_THIN)//边框
			->background(0x4BAAFB)//背景颜色
            ->align(\Vtiful\Kernel\Format::FORMAT_ALIGN_CENTER, \Vtiful\Kernel\Format::FORMAT_ALIGN_VERTICAL_CENTER)//水平垂直居中
            ->toResource();

        //默认格式水平居中
        $this->my_format['global_style_center'] = $format2->fontSize(10)//字体
			->border(\Vtiful\Kernel\Format::BORDER_THIN)//边框
			->align(\Vtiful\Kernel\Format::FORMAT_ALIGN_CENTER, \Vtiful\Kernel\Format::FORMAT_ALIGN_VERTICAL_CENTER)//垂直居中
			->toResource();
			
		//默认格式
		$this->my_format['global_style'] = $format3->fontSize(10)//字体
			->border(\Vtiful\Kernel\Format::BORDER_THIN)//边框
			->align(\Vtiful\Kernel\Format::FORMAT_ALIGN_VERTICAL_CENTER)//垂直居中
			->toResource();
	}
	
	public function add_sheet($type,$sheet_name,$data){
		$this->excel->addSheet($sheet_name);
		$this->insert_header($type);
		$this->insert_data($data);
	}
	
	public function insert_header($type){
		$header = $this->get_excel_field($type);
		$this->keys = array_keys($header);
        $header_len = count($header);
		//最后一列列标
        $end = ['A','B','C','D','E','F','G','H','I','J','K','L','M','N','O','P','Q','R','S','T','U','V','W','X','Y','Z','AA','AB','AC','AD','AE','AF','AG','AH','AI','AJ','AK','AL','AM','AN','AO','AP','AQ','AR','AS','AT','AU','AV','AW','AX','AY','AZ'];
		//首行添加筛选
		$this->excel->autoFilter("A1:".$end[$header_len-1]."1");
        //冻结首行
        $this->excel->freezePanes(1, 0);

		//标题行高
		$this->excel->setRow("A1",25);
		$number_keys = [];
		$center_keys = [];
		$header_key = 0;
		foreach($header as $k=>$v){
			$this->excel->insertText(0, $header_key, $v['name'], NULL, $this->my_format['title_style']);
			//当前列
			$this_key = $end[$header_key];
			//列宽
			$this->excel->setColumn("{$this_key}:{$this_key}", $v['width']);
			//数字格式化字段键值列表
			if(isset($v['format'])){
				if($v['format'] == 'number') {
					$this->number_keys[] = $header_key;
				}
			}
			if(isset($v['align'])){
				if($v['align'] == 'center') {
					$this->center_keys[] = $header_key;
				}
			}
			$header_key++;
		}
		//return $this->output();exit;
	}

	public function insert_data($data){
		if(!$this->page){
			$this->column = 1;
		}
		$array = [];
		$date_arr = ['leave_expire','in_time','out_time'];
		$time_arr = ['add_time'];
		//echo '循环前：'.memory_get_usage();
		foreach($data as $val){
			$datas = [];
			foreach($this->keys as $v){
				if(in_array($v,$date_arr)){
					if(is_numeric($val[$v])){
						$datas[] = date('Y-m-d',$val[$v]);
					}else{
						$datas[] = '-';
					}
				}elseif(in_array($v,$time_arr)){
					if(is_numeric($val[$v])&&$val[$v]>0){
						$datas[] = date('Y-m-d H:i:s',$val[$v]);
					}else{
						$datas[] = '-';
					}
				}else{
					$datas[] = $val[$v];
				}
			}
			$array[] = $datas;
		}
		unset($datas);
		$number_keys = $this->number_keys;
		$center_keys = $this->center_keys;
		$header_key = 0;
		foreach($array as $k=>$v){
			foreach($v as $key=>$val){
				if(in_array($key,$center_keys)){
					$this_style = $this->my_format['global_style_center'];
				}else{
					$this_style = $this->my_format['global_style'];
				}
				//格式格式化避免显示错误
				if(in_array($key,$number_keys)){
					$this->excel->insertText($this->column, $key, intval($val), '0', $this_style);
					continue;
				}
				$this->excel->insertText($this->column, $key, $val, NULL, $this_style);
			}
			//行高
			$this->excel->setRow("A".($this->column+1),20);
			$this->column++;
		}
		unset($array);
		unset($data);
	}
	
	public function output($page=''){
		//分页读取
		if($page){
			$this->page = $page;
			for($i=1;$i<=$page;$i++){
				$this->insert_data(getcache('data_'.$i));
				delcache('data_'.$i);
			}
		}
        // 数据
        $filePath = $this->excel->output();
		$filename = $this->filename;
		delcache('LOCK::download_lock');
        header("Content-Disposition:attachment;filename={$filename}.xlsx");
		ob_clean();
		flush();

        $re = copy($filePath, 'php://output');
        if ($re === false) {
            $err = 'failed to write output';
        } else {
            $err = '';
        }
        @unlink($filePath);
	}
	
	
	//获取缓存目录
	private function get_tmp_dir(){
		$tmp = ini_get('upload_tmp_dir');
		if ($tmp !== False && file_exists($tmp)) {
			return realpath($tmp);
		}
		return realpath(sys_get_temp_dir());
	}
	
	//获取需要导出字段及字段格式
	private function get_excel_field($type=''){
		$field = [
			'id'=>['name'=>'编号','width'=>5,'format'=>'number','align'=>'center'],
			'member_name'=>['name'=>'姓名','width'=>8,'format'=>'','align'=>'center'],
			'department_name'=>['name'=>'部门名称','width'=>16,'format'=>''],
			'position_name'=>['name'=>'岗位名称','width'=>10,'format'=>'','align'=>'center'],
			'status_text'=>['name'=>'在职情况','width'=>10,'format'=>'','align'=>'center'],
			'in_time'=>['name'=>'入职时间','width'=>10,'format'=>'','align'=>'center'],
			'out_time'=>['name'=>'离职时间','width'=>10,'format'=>'','align'=>'center'],
			'years'=>['name'=>'工龄','width'=>5,'format'=>'','align'=>'center'],
			'leave_days'=>['name'=>'年假天数','width'=>10,'format'=>'number','align'=>'center'],
			'leave_expire'=>['name'=>'过期时间','width'=>10,'format'=>'','align'=>'center'],
			
			'money'=>['name'=>'月薪','width'=>6,'format'=>'number','align'=>'center'],
			'housing_money'=>['name'=>'房补','width'=>6,'format'=>'number','align'=>'center'],
			'meal_money'=>['name'=>'餐补','width'=>6,'format'=>'number','align'=>'center'],
			'is_ss'=>['name'=>'社保','width'=>6,'format'=>'','align'=>'center'],
			'bank_name'=>['name'=>'银行名称','width'=>10,'format'=>'','align'=>'center'],
			'bank_code'=>['name'=>'银行卡号','width'=>25,'format'=>'','align'=>'center'],
			'bank_account'=>['name'=>'银行户名','width'=>8,'format'=>'','align'=>'center'],
			'remark'=>['name'=>'薪资备注','width'=>25,'format'=>'','align'=>''],
			
			'sex'=>['name'=>'性别','width'=>4,'format'=>'','align'=>'center'],
			'age'=>['name'=>'年龄','width'=>4,'format'=>'','align'=>'center'],
			'mobile'=>['name'=>'手机号','width'=>12,'format'=>'','align'=>'center'],
			'native_place'=>['name'=>'籍贯','width'=>10,'format'=>'','align'=>'center'],
			'agree_no'=>['name'=>'合同编号','width'=>8,'format'=>'','align'=>'center'],
			'card_id'=>['name'=>'身份证号','width'=>18,'format'=>'','align'=>'center'],
			'address'=>['name'=>'地址','width'=>25,'format'=>'','align'=>''],
			'education'=>['name'=>'学历','width'=>8,'format'=>'','align'=>'center'],
			'major'=>['name'=>'专业','width'=>12,'format'=>'','align'=>'center'],
			're_name'=>['name'=>'紧急联系人','width'=>10,'format'=>'','align'=>'center'],
			're_mobile'=>['name'=>'联系人电话','width'=>12,'format'=>'','align'=>'center'],
			'add_time'=>['name'=>'添加时间','width'=>12,'format'=>'','align'=>'center'],
		];
		switch($type){
			case 'all':
				$need_field = ['id','member_name','department_name','position_name','status_text','in_time','out_time','years','leave_days','leave_expire','money','housing_money','meal_money','is_ss','bank_name','bank_code','housing_money','bank_account','remark','sex','age','mobile','native_place','agree_no','card_id','address','education','major','re_name','re_mobile','add_time'];
				break;
			case 'bank':
				$need_field = ['id','member_name','department_name','position_name','status_text','in_time','out_time','years','money','housing_money','meal_money','is_ss','bank_name','bank_code','housing_money','bank_account','remark'];
				break;
			case 'last_bank':
				$need_field = ['id','member_name','department_name','position_name','status_text','in_time','out_time','years','money','housing_money','meal_money','is_ss','bank_name','bank_code','housing_money','bank_account','remark'];
				break;
			default:
				$need_field = '其他_';
		}
		//返回所需字段
		$return_field = [];
		foreach($need_field as $v){
			$return_field[$v] = $field[$v];
		}
		return $return_field;
	}
	
	//获取文件名
	private function get_excel_filename($type){
		$download_time = date('Y_m_d_H_i_s');
		switch($type){
			case 'all':
				$filename = '员工全部信息表';
				break;
			case 'bank':
				$filename = '员工银行信息表';
				break;
			case 'last_bank':
				$filename = '上个月员工银行信息表';
				break;
			default:
				$filename = '其他_';
		}
		return $filename.$download_time;
	}

	
}

